﻿using OestsCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;

namespace OestsDataBase
{
    public class StudentClassDAL
    {
        const string TABLE_NAME = "T_StudentClass";
        //创建表
        public static void CreateTable()
        {
            string sqlCheck = "if object_id( 'T_StudentClass') is not null select 1 else select 0";
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreacte = @"CREATE TABLE T_StudentClass (student_id bigint primary key ,student_id bigint primary key) ";
                SqlHelper.ExecuteNonQuery(sqlCreacte);
            }
        }

        public static bool ModifyStudentClass(StudentClass sc)
        {
            // string strSql = string.Format("update T_StudentClass set class_id={0} where student_id = @student_id", sc.ClassId);
            // string strSql = string.Format("replace into T_StudentClass set student_id={0},class_id={1}",sc.StudentId, sc.ClassId);
            // string strSql = string.Format("  declare @count int" +
            //"select @count = count(*) from T_StudentClass where student_id = @student_id  " +
            //" if @count > 0 update T_StudentClass set class_id = {0} where student_id = @student_id " +
            //"   else insert into T_StudentClass (student_id, class_id) values({1},{0}) ",sc.StudentId,sc.ClassId);

            string strSql = string.Format(" declare @count int"
                + " select @count = count(*) from T_StudentClass where student_id = {0}"
                + " if @count > 0 update T_StudentClass set class_id = {1} where student_id = {0}"
                + "  else insert into T_StudentClass(student_id, class_id) values({0}, {1}) ", sc.StudentId, sc.ClassId);

            int n = SqlHelper.ExecuteNonQuery(strSql);
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 添加一条数据返回自增列ID
        /// </summary>
        /// <param name="cls"></param>
        /// <returns></returns>
        //public static int Add(StudentClass cls)
        //{
        //    //先将自增字段设置为从0开始
        //    DBManager.Checkident(TABLE_NAME);
        //    string strSql = string.Format("insert into {0} (name, teacher_id) values('{1}',{2});SELECT @@Identity", TABLE_NAME, cls.Name, cls.TeacherID);
        //    object obj = SqlHelper.ExecuteScalar(strSql);
        //    return Int32.Parse(obj.ToString());
        //}s

        public static int AddStudentClass(StudentClass sc)
        {
            string strSql = string.Format("insert into {0} (student_id, class_id) values({1},{2});SELECT @@Identity", TABLE_NAME, sc.StudentId, sc.ClassId);
            object obj = SqlHelper.ExecuteNonQuery(strSql);
            return Int32.Parse(obj.ToString());
        }

        public static int AddStudentClass(List<StudentClass> list)
        {
            if (list == null || list.Count == 0) return 0;
            string vaules = "";
            foreach (var item in list)
            {
                vaules += $"({item.StudentId},{item.ClassId}),";
            }
            vaules = vaules.Substring(0, vaules.Length - 1);

            string strSql = string.Format("insert into {0} (student_id, class_id) values{1};SELECT @@Identity", TABLE_NAME, vaules);
            object obj = SqlHelper.ExecuteNonQuery(strSql);
            return Int32.Parse(obj.ToString());
        }



        /// <summary>
        /// 根据学生id删除学员班级信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteByStdID(int stdId)
        {
            //string strSql = string.Format("delete from "+ TABLE_NAME + " where student_id = @student_id");
            //int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@student_id", stdId));

            string strSql = string.Format("delete from " + TABLE_NAME + " where student_id = {0}", stdId);
            int n = SqlHelper.ExecuteNonQuery(strSql);

            if (n != 0)
            {
                return true;
            }
            return false;

        }

        /// <summary>
        /// 一次删除多个学生的班级信息
        /// </summary>
        /// <param name="stdIds"></param>
        /// <returns></returns>
        public static bool DeleteByStdIds(IEnumerable<int> stdIds)
        {
            string idStr = string.Join(",", stdIds);

            string strSql = $"delete from {TABLE_NAME} where student_id in ({idStr})";
            int n = SqlHelper.ExecuteNonQuery(strSql);

            if (n != 0)
            {
                return true;
            }
            return false;

        }


        /// <summary>
        /// 根据班级id删除学员班级信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteByClassID(int clsId)
        {
            string strSql = string.Format("delete from " + TABLE_NAME + " where class_id = {0}", clsId);
            object n = SqlHelper.ExecuteScalar(strSql);
            if (n != null)
            {
                int stdId = int.Parse(n.ToString());
                return stdId > 0;
            }

            return false;

            //if (stdId > 0)
            //{
            //    return UserDAL.DeleteUser(stdId);
            //}

        }


        public static void DeleteByClassID(List<int> clsIdList)
        {
            string idStr = string.Join(",", clsIdList);
            string strSql = string.Format("delete from " + TABLE_NAME + " where class_id in ({0})", idStr);
            SqlHelper.ExecuteScalar(strSql);
        }

        public static List<StudentClass> GetData()
        {
            List<StudentClass> list = new List<StudentClass>();
            string strSql = "select * from T_StudentClass ";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
                StudentClass cls = ReadData(dr);
                list.Add(cls);
            }
            return list;
        }
        private static StudentClass ReadData(DataRow dr)
        {
            StudentClass sc = new StudentClass();
            sc.StudentId = Int32.Parse(dr["student_id"].ToString());
            sc.ClassId = dr["class_id"].Equals(DBNull.Value) ? 0 : int.Parse(dr["class_id"].ToString());
            return sc;
        }
    }
}
